package com.kcsj.dao;
//201902104050 姜瑞临
import com.kcsj.domain.Order;
import com.kcsj.domain.TaskList;
import com.kcsj.domain.user.Employee;
import com.kcsj.service.user.EmployeeService;
import com.kcsj.util.JdbcHelper;

import java.sql.*;
import java.util.Collection;
import java.util.LinkedList;

public class TaskListDao {
    public static TaskListDao wayBillDao = new TaskListDao();
    public static TaskListDao getInstance() {return wayBillDao;}

    public boolean add(TaskList taskList, Connection conn) throws SQLException {
        //使用预编译创建SQL语句
        String create = "insert into TaskList(taskListNo,orderNo,taskListStatus,deliveryPerson_id) " +
                "values (?,?,?,?) ";
        PreparedStatement statement = conn.prepareStatement(create);
        //执行SQL语句并返回结果和关闭连接
        try {
            statement.setString(1, taskList.getTaskListNo());
            statement.setString(2, taskList.getOrder().getOrderNo());
            statement.setString(3, taskList.getTaskListStatus());
            statement.setInt(4, taskList.getEmployee().getId());
            int rowsAffected = statement.executeUpdate();
            return rowsAffected > 0;
        }finally {
            JdbcHelper.close(statement);
        }
    }
    public boolean delete(String taskListNo, Connection conn) throws SQLException {
        //创建删除语句
        String delete = "delete from taskList where taskListNo = " + taskListNo;
        //在连接上创建语句盒子对象
        Statement statement = conn.createStatement();
        //执行SQL语句
        int i = statement.executeUpdate(delete);
        //关闭连接
        JdbcHelper.close(statement,conn);
        return (i>0);
    }
    public boolean update(TaskList taskList, Connection conn) throws SQLException {
        //使用预编译创建SQL语句
        String update = "update taskList set taskListStatus = ?,deliveryPerson_id = ?) where TaskListNo = " + taskList.getTaskListNo();
        PreparedStatement statement = conn.prepareStatement(update);
        //执行SQL语句并返回结果和关闭连接
        try {
            statement.setString(1, taskList.getTaskListStatus());
            statement.setInt(2, taskList.getEmployee().getId());
            int rowsAffected = statement.executeUpdate();
            return rowsAffected > 0;
        }finally {
            JdbcHelper.close(statement);
        }
    }

    public boolean updateStatus(String orderNo,
                                String status,
                                Connection conn) throws SQLException {
        //sql语句
        String delay_sql = "update taskList set taskListStatus=? where orderNo = ?";
        System.out.println(status);
        PreparedStatement pstmt = conn.prepareStatement(delay_sql);
        pstmt.setString(1,status);
        pstmt.setString(2,orderNo);
        int rowsAffected = pstmt.executeUpdate();
        JdbcHelper.close(pstmt);
        return rowsAffected>0;
    }

    public TaskList find(String taskListNo, Connection conn) throws SQLException {
    //sql语句
    String find_sql = "SELECT * FROM TaskList where TaskListNo = ? ";
    PreparedStatement statement = conn.prepareStatement(find_sql);
    statement.setString(1,taskListNo);
    ResultSet resultSet = statement.executeQuery();
    TaskList taskList = null;
    if (resultSet.next()) {
        taskList = new TaskList(
                resultSet.getString("taskListNo"),
                OrderDao.getInstance().find(resultSet.getString("orderNo"),conn),
                resultSet.getString("taskListStatus"),
                resultSet.getString("createDatetime"),
                EmployeeService.getInstance().findInfo(resultSet.getInt("deliveryPerson_id"))
        );
    }
    JdbcHelper.close(statement);
    return taskList;
    }

    public Collection<TaskList> findAll(Integer id,Connection conn) throws SQLException {
        //创建集合类对象，用来保存并排序所有获得的department对象
        Collection<TaskList> taskLists = new LinkedList<>();
        //创建查询的主句
        String select = "SELECT * from TaskList where deliveryperson_id = " + id;
        //在连接上创建语句盒子对象
        PreparedStatement statement = conn.prepareStatement(select);
        //执行SQL语句
        ResultSet results = statement.executeQuery();
        //遍历resultSet，并将结果写入对象存进集合内
        while (results.next()){
            String taskListNo = results.getString("taskListNo");
            Order order = OrderDao.getInstance().find(results.getString("orderNo"),conn);
            String taskListStatus = results.getString("taskListStatus");
            String createDatetime = results.getString("createDatetime");
            Employee employee = EmployeeService.getInstance().findInfo(results.getInt("deliveryPerson_id"));
            TaskList taskList = new TaskList(taskListNo,order, taskListStatus,createDatetime, employee);
            taskLists.add(taskList);
        }
        //关闭资源
        JdbcHelper.close(results,statement,null);
        //返回获得的集合对象
        return taskLists;
    }
}
